package edu.fatec.veterinaria.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.swing.JOptionPane;

import edu.fatec.veterinaria.model.Animal;
import edu.fatec.veterinaria.model.Especie;
import edu.fatec.veterinaria.view.PesquisaAnimal;

public class AnimalDAO implements IAnimalDAO {




	/* (non-Javadoc)
	 * @see edu.fatec.veterinaria.dao.IAnimalDAO#insereAnimal(edu.fatec.veterinaria.model.Animal)
	 */
	@Override
	public boolean insereAnimal(Animal animal) {
		try{
			Connection conexao = GenericDao.getConnection();
			String sql ="insert into Animal" +
			"(codCliente,codEspecie,nome,raca,sexo,dataNasc)" +
			"values (?,?,?,?,?,?)";
			PreparedStatement prepare = conexao.prepareStatement(sql);

			prepare.setString(1, animal.getCodCliente());
			prepare.setInt(2, animal.getCodEspecie());
			prepare.setString(3, animal.getNome());
			prepare.setString (4,animal.getRaca());
			prepare.setInt(5, animal.getSexo());
			prepare.setDate(6, animal.getDataNasc());

			prepare.execute();
			prepare.close();
			return true;

		}catch (SQLException e){
			e.printStackTrace();
			return false;
		}
	}


	/* (non-Javadoc)
	 * @see edu.fatec.veterinaria.dao.IAnimalDAO#excluirAnimais(java.lang.String)
	 */
	@Override
	public void excluirAnimais(String codCliente){
		try{
			Connection conexao = GenericDao.getConnection();

			String sql = "delete Animal " +
			"where codCliente like ? ";

			PreparedStatement prepare = conexao.prepareStatement(sql);
			prepare.setString(1, codCliente);
			prepare.execute();
			prepare.close();

		}catch(SQLException s){
			s.printStackTrace();
		}
	}

	/* (non-Javadoc)
	 * @see edu.fatec.veterinaria.dao.IAnimalDAO#atualizaAnimal(edu.fatec.veterinaria.model.Animal)
	 */
	@Override
	public void atualizaAnimal(Animal a) {
		try{

			Connection conexao = GenericDao.getConnection();

			String sql = "update Animal set nome = ?,raca = ? ,"+
			"codEspecie = ? , sexo = ? , dataNasc = ? where codigo = ?";

			PreparedStatement prepare = conexao.prepareStatement(sql);

			prepare.setString(1, a.getNome());
			prepare.setString(2, a.getRaca());
			prepare.setInt(3, a.getCodEspecie());
			prepare.setInt(4, a.getSexo());
			prepare.setDate(5, a.getDataNasc());
			prepare.setInt(6, a.getCodigoAnimal());

			prepare.execute();
			prepare.close();


		}catch (SQLException e) {
			e.printStackTrace();
		}

	}

	/* (non-Javadoc)
	 * @see edu.fatec.veterinaria.dao.IAnimalDAO#removeAnimal(edu.fatec.veterinaria.model.Animal)
	 */
	@Override
	public boolean removeAnimal(Animal a) {
		try{
			Connection conexao = GenericDao.getConnection();

			String sql = "delete Animal where codigo  = ?";

			PreparedStatement prepare = conexao.prepareStatement(sql);

			prepare.setInt(1, a.getCodigoAnimal());

			prepare.execute();
			prepare.close();

			return true;
		}catch (SQLException s){
			return false;
		}

	}





	/* (non-Javadoc)
	 * @see edu.fatec.veterinaria.dao.IAnimalDAO#pesquisaAnimal(int)
	 */
	@Override
	public Animal pesquisaAnimal(int codAnimal){
		Animal animal = new Animal();
		try{
			Connection conexao = GenericDao.getConnection();
			String sql = "select codCliente,codigo,codEspecie," +
			"nome,raca,sexo,dataNasc from Animal where codigo = ?";
			PreparedStatement ps0 = conexao.prepareStatement(sql);
			ps0.setInt(1, codAnimal);
			ps0.execute();
			ResultSet rs = ps0.getResultSet();
			if(rs.next()){
				animal.setCodCliente(rs.getString(1));
				animal.setCodigoAnimal(rs.getInt(2));
				animal.setCodEspecie(rs.getInt(3));
				animal.setNome(rs.getString(4));
				animal.setRaca(rs.getString(5));
				animal.setSexo(Integer.parseInt(rs.getString(6)
						.replace("M", "0").replace("F", "1")));
				animal.setDataNasc(rs.getDate(7));
			}
			ps0.close();
			return animal;

		}catch(Exception e){
			return null;

		}
	}

	/* (non-Javadoc)
	 * @see edu.fatec.veterinaria.dao.IAnimalDAO#pesquisaAnimalNome(java.lang.String)
	 */
	@Override
	public Animal pesquisaAnimalNome(String nomeAnimal){

		try{
			Connection conexao = GenericDao.getConnection();
			Animal animal = new Animal();
			PreparedStatement ps0 = conexao.prepareStatement("select * from Animal where nome = ?");
			ps0.setString(1, nomeAnimal);
			ps0.execute();
			ResultSet rs = ps0.getResultSet();
			if(rs.next()){
				animal.setCodCliente(rs.getString("codCliente"));
				animal.setCodigoAnimal(rs.getInt("codigo"));
				animal.setCodEspecie(rs.getInt("codEspecie"));
				animal.setNome(rs.getString("nome"));
				animal.setRaca(rs.getString("raca"));
				animal.setSexo(Integer.parseInt(rs.getString("sexo").replace("M", "0").replace("F", "1")));
				animal.setDataNasc(rs.getDate("dataNasc"));
			}
			ps0.close();
			return animal;
		}catch(Exception e){
			return null;

		}

	}

	/* (non-Javadoc)
	 * @see edu.fatec.veterinaria.dao.IAnimalDAO#listaEspecie()
	 */
	@Override
	public List<Especie> listaEspecie(){

		try{
			Connection conexao = GenericDao.getConnection();
			List<Especie> listaEspecie = new ArrayList<Especie>();
			PreparedStatement ps = 
				conexao.prepareStatement("select codigo, especie from especie");
			ps.execute();
			ResultSet rs = ps.getResultSet();
			while (rs.next()){
				Especie especie = new Especie();
				especie.setCodigoEspecie(rs.getInt(1));
				especie.setTipoEspecie(rs.getString(2));
				listaEspecie.add(especie);
			}
			ps.close();
			return listaEspecie;
		}catch (Exception e){
			e.printStackTrace();
			return null;
		}


	}



	/* (non-Javadoc)
	 * @see edu.fatec.veterinaria.dao.IAnimalDAO#pesquisarAnimal(java.lang.String, java.lang.String)
	 */
	@Override
	public boolean pesquisarAnimal(String nome, String codCliente) {
		try{
			Connection con = GenericDao.getConnection();
			String sql = "select nome from Animal where codCliente like ? and nome like ?";
			PreparedStatement prepare = con.prepareStatement(sql);
			prepare.setString(1, codCliente);
			prepare.setString(2, nome);
			prepare.execute();
			ResultSet result = prepare.getResultSet();
			boolean existente;
			if (result.next()){
				existente = false;
			}else{
				existente = true;
			}
			prepare.close();
			return existente;

		}catch (SQLException e) {
			return false;
		}

	}



	/* (non-Javadoc)
	 * @see edu.fatec.veterinaria.dao.IAnimalDAO#buscarAnimal(java.lang.String, java.lang.String)
	 */
	@Override
	public Animal buscarAnimal(String animal, String codigoCliente) {
		try{
			Connection conexao = GenericDao.getConnection();
			String sql =  "select Animal.nome,Animal.raca,Animal.sexo,Animal.codEspecie," +
			"Convert (char(10),Animal.dataNasc,103) as Nascimento" +
			" from Cliente inner join Animal on  Cliente.cpf = Animal.codCliente" +
			"where Cliente.cpf like ? and Animal.nome like ?";
			PreparedStatement prepare = conexao.prepareStatement(sql);
			prepare.setString(1, codigoCliente);
			prepare.setString(1, animal);
			prepare.execute();
			ResultSet result = prepare.getResultSet();
			Animal a = new Animal();
			if (result.next()){
				a.setNome(result.getString(1));
				a.setRaca(result.getString(2));
				a.setSexo(result.getInt(3));
				a.setCodEspecie(result.getInt(4));
				a.setDataNasc(result.getDate(5));
			}else {
				a = null;
			}
			return a ;
		}catch (SQLException e) {
			return null;
		}

	}


	/* (non-Javadoc)
	 * @see edu.fatec.veterinaria.dao.IAnimalDAO#listarAnimalNome(java.lang.String)
	 */
	@Override
	public List<Animal> listarAnimalNome(String nomeAnimal) {
		try{
			Connection conexao = GenericDao.getConnection();
			String sql = "select nome,raca,sexo,codEspecie," +
			"dataNasc,codCliente from Animal " +
			" where nome like ?";
			PreparedStatement prepare = conexao.prepareStatement(sql);
			prepare.setString(1, nomeAnimal);
			prepare.execute();
			ResultSet result = prepare.getResultSet();
			List<Animal> lista = new ArrayList<Animal>();
			while (result.next()){
				Animal a = new Animal();
				a.setNome(result.getString(1));
				a.setRaca(result.getString(2));
				a.setSexo(result.getInt(3));
				a.setCodEspecie(result.getInt(4));
				a.setDataNasc(result.getDate(5));
				a.setCodCliente(result.getString(6));
				lista.add(a);
			}
			if (lista.isEmpty()){
				return null;
			}else{
				return lista;
			}

		}catch (SQLException s){
			s.printStackTrace();
			return null;
		}

	}



	/* (non-Javadoc)
	 * @see edu.fatec.veterinaria.dao.IAnimalDAO#listarAnimal(java.lang.String, int)
	 */
	@Override
	public List<Animal> listarAnimal(String codigo,int valor) {
		try{
			Connection conexao = GenericDao.getConnection();
			List<Animal> animais = new ArrayList<Animal>();
			String sql = "" ;
			if (valor == 0){
				sql =  "select codigo,nome,raca,sexo" +
				",dataNasc,codEspecie,codCliente from Animal " +
				"where codCliente like ?";

			}else if (valor == 1 ) {
				sql = "select codigo,nome,raca,sexo" +
				",dataNasc,codEspecie,codCliente from Animal " +
				"where nome like ?";
				codigo = "%" + codigo + "%";
			}
			PreparedStatement prepare = conexao.prepareStatement(sql);
			prepare.setString(1,codigo);
			prepare.execute();
			ResultSet result = prepare.getResultSet();
			while (result.next()){
				Animal a = new Animal();
				a.setCodigoAnimal(result.getInt(1));
				a.setNome(result.getString(2));
				a.setRaca(result.getString(3));
				a.setSexo(result.getInt(4));
				a.setDataNasc(result.getDate(5));
				a.setCodEspecie(result.getInt(6));
				a.setCodCliente(result.getString(7));
				animais.add(a);
			}
			prepare.close();
			if (animais.isEmpty()){
				return null;
			}else {
				return animais;
			}
		}catch (SQLException s){
			s.printStackTrace();
			JOptionPane.showMessageDialog(null, "Erro na consulta");
			return null;
		}
	}


	public String buscarEspecie(int tipoAnimal) {
		try{
			String especie="";
			Connection conexao = GenericDao.getConnection();
			String sql = "select especie from Especie where codigo = ?";
			PreparedStatement prepare = conexao.prepareStatement(sql);
			prepare.setInt(1, tipoAnimal);
			prepare.execute();
			ResultSet result = prepare.getResultSet();
			if (result.next()){
				especie = result.getString(1);
			}
			return especie;
		}catch (SQLException e) {
			e.printStackTrace();
			return "";
		}

	}







}

